﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMonitor_PerfmonHourlySummaries]
#-- Purpose:		Rolls detail information up into an hourly summary containing
#--					average and stdevp on either side of the average.
#--	Last Update:	08/05/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMonitor_PerfmonHourlySummaries]
AS

SET NOCOUNT ON

INSERT INTO [dbo].[tblMonitor_PerfmonHourlySummary]
	SELECT		CAST(CONVERT(VARCHAR(10), pm.date_inserted, 101) + ' ' + CAST(DATEPART(hour, pm.date_inserted) as varchar(2)) + ':00' AS DATETIME) date_inserted,

				CAST(ROUND(CASE WHEN AVG(pm.buffer_cache_hit_ratio) < 0 THEN 0 WHEN AVG(pm.buffer_cache_hit_ratio) > 100 THEN 100 ELSE AVG(pm.buffer_cache_hit_ratio) END, 3) as float) buffer_cache_hit_ratio_AVG,
				CAST(ROUND(STDEVP(pm.buffer_cache_hit_ratio), 3) as float) buffer_cache_hit_ratio_STDEVP,
				
				(CASE WHEN AVG(pm.page_life_expectancy) < 0 THEN 0 ELSE AVG(pm.page_life_expectancy) END) page_life_expectancy_AVG,
				ROUND(STDEVP(pm.page_life_expectancy), 0) page_life_expectancy_STDEVP,

				ROUND(CASE WHEN AVG(pm.logins_per_sec) < 0 THEN 0 ELSE AVG(pm.logins_per_sec) END, 2) logins_per_sec_AVG,
				ROUND(STDEVP(pm.logins_per_sec), 2) logins_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.logouts_per_sec) < 0 THEN 0 ELSE AVG(pm.logouts_per_sec) END, 2) logouts_per_sec_AVG,
				ROUND(STDEVP(pm.logouts_per_sec), 2) logouts_per_sec_STDEVP,

				ROUND(CASE WHEN AVG(pm.user_connections) < 0 THEN 0 ELSE AVG(pm.user_connections) END, 0) user_connections_AVG,
				ROUND(STDEVP(pm.user_connections), 0) user_connections_STDEVP,
				
				AVG(pm.optimizer_memory_KB) optimizer_memory_KB,
				AVG(pm.sql_cache_memory_KB) sql_cache_memory_KB,
				AVG(pm.target_server_memory_KB) target_server_memory_KB,
				AVG(pm.total_server_memory_KB) total_server_memory_KB,

				ROUND(CASE WHEN AVG(pm.sql_compilations_per_sec) < 0 THEN 0 ELSE AVG(pm.sql_compilations_per_sec) END, 2) sql_compilations_per_sec_AVG,
				ROUND(STDEVP(pm.sql_compilations_per_sec), 2) sql_compilations_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.sql_recompilations_per_sec) < 0 THEN 0 ELSE AVG(pm.sql_recompilations_per_sec) END, 2) sql_recompilations_per_sec_AVG,
				ROUND(STDEVP(pm.sql_recompilations_per_sec), 2) sql_recompilations_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.batch_requests_per_sec) < 0 THEN 0 ELSE AVG(pm.batch_requests_per_sec) END, 2) batch_requests_per_sec_AVG,
				ROUND(STDEVP(pm.batch_requests_per_sec), 2) batch_requests_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.number_of_deadlocks_per_sec) < 0 THEN 0 ELSE AVG(pm.number_of_deadlocks_per_sec) END, 2) number_of_deadlocks_per_sec_AVG,
				ROUND(STDEVP(pm.number_of_deadlocks_per_sec), 2) number_of_deadlocks_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.average_wait_time_ms) < 0 THEN 0 ELSE AVG(pm.average_wait_time_ms) END, 2) average_wait_time_ms_AVG,
				ROUND(STDEVP(pm.average_wait_time_ms), 2) average_wait_time_ms_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.lock_waits_per_sec) < 0 THEN 0 ELSE AVG(pm.lock_waits_per_sec) END, 2) lock_waits_per_sec_AVG,
				ROUND(STDEVP(pm.lock_waits_per_sec), 2) lock_waits_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.lock_wait_time_ms) < 0 THEN 0 ELSE AVG(pm.lock_wait_time_ms) END, 2) lock_wait_time_ms_AVG,
				ROUND(STDEVP(pm.lock_wait_time_ms), 2) lock_wait_time_ms_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.full_scans_per_sec) < 0 THEN 0 ELSE AVG(pm.full_scans_per_sec) END, 2) full_scans_per_sec_AVG,
				ROUND(STDEVP(pm.full_scans_per_sec), 2) full_scans_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.lazy_writes_per_sec) < 0 THEN 0 ELSE AVG(pm.lazy_writes_per_sec) END, 2) lazy_writes_per_sec_AVG,
				ROUND(STDEVP(pm.lazy_writes_per_sec), 2) lazy_writes_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.page_reads_per_sec) < 0 THEN 0 ELSE AVG(pm.page_reads_per_sec) END, 2) page_reads_per_sec_AVG,
				ROUND(STDEVP(pm.page_reads_per_sec), 2) page_reads_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.page_writes_per_sec) < 0 THEN 0 ELSE AVG(pm.page_writes_per_sec) END, 2) page_writes_per_sec_AVG,
				ROUND(STDEVP(pm.page_writes_per_sec), 2) page_writes_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.checkpoint_pages_per_sec) < 0 THEN 0 ELSE AVG(pm.checkpoint_pages_per_sec) END, 2) checkpoint_pages_per_sec_AVG,
				ROUND(STDEVP(pm.checkpoint_pages_per_sec), 2) checkpoint_pages_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.page_splits_per_sec) < 0 THEN 0 ELSE AVG(pm.page_splits_per_sec) END, 2) page_splits_per_sec_AVG,
				ROUND(STDEVP(pm.page_splits_per_sec), 2) page_splits_per_sec_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.transactions_per_sec) < 0 THEN 0 ELSE AVG(pm.transactions_per_sec) END, 2) transactions_per_sec_AVG,
				ROUND(STDEVP(pm.transactions_per_sec), 2) transactions_per_sec_STDEVP,
			
				ROUND(CASE WHEN AVG(pm.cpu_busy) < 0 THEN 0 ELSE AVG(pm.cpu_busy) END, 2) cpu_busy_AVG,
				ROUND(STDEVP(pm.cpu_busy), 2) cpu_busy_STDEVP,
				
				ROUND(CASE WHEN AVG(pm.io_busy) < 0 THEN 0 ELSE AVG(pm.io_busy) END, 2) io_busy_AVG,
				ROUND(STDEVP(pm.io_busy), 2) io_busy_STDEVP
	FROM		[dbo].[vwMonitor_Perfmon] pm (NOLOCK)
	WHERE		pm.date_inserted < CAST(CONVERT(VARCHAR(10), GETDATE(), 101) + ' ' + CAST(DATEPART(hour, GETDATE()) as varchar(2)) + ':00' AS DATETIME)
				AND (
					pm.date_inserted >= (SELECT DATEADD(hour, 1, MAX(date_inserted)) FROM [dbo].[tblMonitor_PerfmonHourlySummary])
					OR NOT EXISTS (SELECT TOP 1 * FROM [dbo].[tblMonitor_PerfmonHourlySummary])
				)
	GROUP BY	CAST(CONVERT(VARCHAR(10), pm.date_inserted, 101) + ' ' + CAST(DATEPART(hour, pm.date_inserted) as varchar(2)) + ':00' AS DATETIME)

SET NOCOUNT OFF